package dao;

import pojo.Book;
import pojo.Review;
import util.DBHelper;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ReviewDAO {

    private static final String processTable = "review";
    private Connection c = null;
    private PreparedStatement ps = null;
    private Statement s = null;
    private ResultSet rs = null;

    //增
    public void add(Review review) throws Exception {
        String sql = "insert into " + processTable + " (rw_id, rw_title,  rw_content, rw_type, rw_time, rw_st_id, rw_st_name, rw_bk_id, rw_like, rw_discuss, rw_img) values(null, ?, ?, ?, ?, ?, ?, ?, 0, 0, ?)";
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, review.getTitle());
            ps.setString(2, review.getContent());
            ps.setString(3, review.getType());
            ps.setString(4, review.getTime());
            ps.setInt(5, review.getSt_id());
            ps.setString(6, review.getSt_name());
            ps.setInt(7, review.getBk_id());
            ps.setString(8, review.getImg());
            ps.execute();
            rs = ps.getGeneratedKeys();
            if (rs.next()) {
                int id = rs.getInt(1);
                review.setId(id);
            }
            rs.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally{
            // 关闭资源
            if (rs != null){
                rs.close();
                ps.close();
            }
        }
    }

    //查：获取总数
    public int getTotal() {
        int total = 0;
        try {
            c = DBHelper.getConnection();
            s = c.createStatement();
            String sql = "select count(*) from " + processTable;
            rs = s.executeQuery(sql);
            while (rs.next()) {
                total = rs.getInt(1);
            }
            s.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return total;
    }

    //查：获取指定书籍的评论总数
    public int getTotal(int bk_id) {
        int total = 0;
        try {
            c = DBHelper.getConnection();
            s = c.createStatement();
            String sql = "select count(*) from " + processTable + " where rw_bk_id=" + bk_id;
            rs = s.executeQuery(sql);
            while (rs.next()) {
                total = rs.getInt(1);
            }
            s.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return total;
    }

    //查：指定区间对象匹配bk_id
    public List<Review> list(int start, int count, int bk_id) {
        List<Review> reviews = new ArrayList<Review>();
        try {
            c = DBHelper.getConnection();
            String sql = "select * from " + processTable + " where rw_bk_id=" + bk_id + " order by rw_id desc limit ?,? ";
            ps = c.prepareStatement(sql);
            ps.setInt(1, start);
            ps.setInt(2, count);
            rs = ps.executeQuery();
            while (rs.next()) {
                Review review = new Review();
                review.setId(rs.getInt("rw_id"));
                review.setTitle(rs.getString("rw_title"));
                review.setContent(rs.getString("rw_content"));
                review.setType(rs.getString("rw_type"));
                review.setTime(rs.getString("rw_time"));
                review.setLike(rs.getInt("rw_like"));
                review.setDiscuss(rs.getInt("rw_discuss"));
                review.setSt_id(rs.getInt("rw_st_id"));
                review.setSt_name(rs.getString("rw_st_name"));
                review.setBk_id(rs.getInt("rw_bk_id"));
                review.setImg(rs.getString("rw_img"));

                review.setType2chn(Review.getType2chnByType(review.getType()));   //特殊需求，不做复用：下面用来设置中文的评论类型来显示

                reviews.add(review);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return reviews;
    }

    //查：指定区间对象匹配bk_id、type
    public List<Review> listByCategory(int start, int count, int bk_id, String category) {
        List<Review> reviews = new ArrayList<Review>();
        try {
            c = DBHelper.getConnection();
            String sql = "select * from " + processTable + " where rw_bk_id=" + bk_id + " and rw_type='" + category + "' order by rw_id desc limit ?,? ";
            ps = c.prepareStatement(sql);
            ps.setInt(1, start);
            ps.setInt(2, count);
            rs = ps.executeQuery();
            while (rs.next()) {
                Review review = new Review();
                review.setId(rs.getInt("rw_id"));
                review.setTitle(rs.getString("rw_title"));
                review.setContent(rs.getString("rw_content"));
                review.setType(rs.getString("rw_type"));
                review.setTime(rs.getString("rw_time"));
                review.setLike(rs.getInt("rw_like"));
                review.setDiscuss(rs.getInt("rw_discuss"));
                review.setSt_id(rs.getInt("rw_st_id"));
                review.setSt_name(rs.getString("rw_st_name"));
                review.setBk_id(rs.getInt("rw_bk_id"));
                review.setImg(rs.getString("rw_img"));

                review.setType2chn(Review.getType2chnByType(review.getType()));   //特殊需求，不做复用：下面用来设置中文的评论类型来显示

                reviews.add(review);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return reviews;
    }

    //查：指定区间对象匹配bk_id、排序方式
    public List<Review> listBySort(int start, int count, int bk_id, String sort) {
        String sql = null;
        switch (sort){
            case "discuss" :
                sql = "select * from " + processTable + " where rw_bk_id=" + bk_id + " order by rw_discuss desc limit ?,? ";
                break;
            case "like" :
                sql = "select * from " + processTable + " where rw_bk_id=" + bk_id + " order by rw_like desc limit ?,? ";
                break;
            case "asc" :
                sql = "select * from " + processTable + " where rw_bk_id=" + bk_id + " order by rw_id limit ?,? ";
                break;
            default:
                sql = "select * from " + processTable + " where rw_bk_id=" + bk_id + " order by rw_id desc limit ?,? ";
        }

        List<Review> reviews = new ArrayList<Review>();
        try {
            c = DBHelper.getConnection();

            ps = c.prepareStatement(sql);
            ps.setInt(1, start);
            ps.setInt(2, count);
            rs = ps.executeQuery();
            while (rs.next()) {
                Review review = new Review();
                review.setId(rs.getInt("rw_id"));
                review.setTitle(rs.getString("rw_title"));
                review.setContent(rs.getString("rw_content"));
                review.setType(rs.getString("rw_type"));
                review.setTime(rs.getString("rw_time"));
                review.setLike(rs.getInt("rw_like"));
                review.setDiscuss(rs.getInt("rw_discuss"));
                review.setSt_id(rs.getInt("rw_st_id"));
                review.setSt_name(rs.getString("rw_st_name"));
                review.setBk_id(rs.getInt("rw_bk_id"));
                review.setImg(rs.getString("rw_img"));

                review.setType2chn(Review.getType2chnByType(review.getType()));   //特殊需求，不做复用：下面用来设置中文的评论类型来显示

                reviews.add(review);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return reviews;
    }

    //查：指定区间对象匹配bk_id、类别、排序方式
    public List<Review> list(int start, int count, int bk_id, String category, String sort) {
        String sql = null;
        switch (sort){
            case "discuss" :
                sql = "select * from " + processTable + " where rw_bk_id=" + bk_id + " and rw_type='" + category + "' order by rw_discuss desc limit ?,? ";
                break;
            case "like" :
                sql = "select * from " + processTable + " where rw_bk_id=" + bk_id + " and rw_type='" + category + "' order by rw_like desc limit ?,? ";
                break;
            case "asc" :
                sql = "select * from " + processTable + " where rw_bk_id=" + bk_id + " and rw_type='" + category + "' order by rw_id limit ?,? ";
                break;
            default:
                sql = "select * from " + processTable + " where rw_bk_id=" + bk_id + " and rw_type='" + category + "' order by rw_id desc limit ?,? ";
        }
        List<Review> reviews = new ArrayList<Review>();
        try {
            c = DBHelper.getConnection();

            ps = c.prepareStatement(sql);
            ps.setInt(1, start);
            ps.setInt(2, count);
            rs = ps.executeQuery();
            while (rs.next()) {
                Review review = new Review();
                review.setId(rs.getInt("rw_id"));
                review.setTitle(rs.getString("rw_title"));
                review.setContent(rs.getString("rw_content"));
                review.setType(rs.getString("rw_type"));
                review.setTime(rs.getString("rw_time"));
                review.setLike(rs.getInt("rw_like"));
                review.setDiscuss(rs.getInt("rw_discuss"));
                review.setSt_id(rs.getInt("rw_st_id"));
                review.setSt_name(rs.getString("rw_st_name"));
                review.setBk_id(rs.getInt("rw_bk_id"));
                review.setImg(rs.getString("rw_img"));

                review.setType2chn(Review.getType2chnByType(review.getType()));   //特殊需求，不做复用：下面用来设置中文的评论类型来显示

                reviews.add(review);
            }
            rs.close();
            ps.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return reviews;
    }

    // 查：根据id获得详情
    public Review getReviewById(int id) {
        try {
            c = DBHelper.getConnection();
            String sql = "select * from " + processTable + " where rw_id=?;"; // SQL语句
            ps = c.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                Review review = new Review();
                review.setId(rs.getInt("rw_id"));
                review.setTitle(rs.getString("rw_title"));
                review.setContent(rs.getString("rw_content"));
                review.setType(rs.getString("rw_type"));
                review.setTime(rs.getString("rw_time"));
                review.setLike(rs.getInt("rw_like"));
                review.setDiscuss(rs.getInt("rw_discuss"));
                review.setSt_id(rs.getInt("rw_st_id"));
                review.setSt_name(rs.getString("rw_st_name"));
                review.setBk_id(rs.getInt("rw_bk_id"));
                review.setImg(rs.getString("rw_img"));
                review.setType2chn(Review.getType2chnByType(review.getType()));   //特殊需求，不做复用：下面用来设置中文的评论类型来显示
                return review;
            } else {
                return null;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        } finally {
            // 释放数据集对象
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
            // 释放语句对象
            if (s != null) {
                try {
                    s.close();
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    //更新：review的like
    public void updateLike(Review review, int id) throws Exception {
        String sql = "update " + processTable + " set rw_like = ? where rw_id = ?";
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(sql);
            ps.setInt(1, review.getLike());
            ps.setInt(2, id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ps.close();
        }
    }

    //更新：review的discuss
    public void updateDiscuss(Review review, int id) throws Exception {
        String sql = "update " + processTable + " set rw_discuss = ? where rw_id = ?";
        try {
            c = DBHelper.getConnection();
            ps = c.prepareStatement(sql);
            ps.setInt(1, review.getDiscuss());
            ps.setInt(2, id);
            ps.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ps.close();
        }
    }

}
